


import datetime
import requests
from db_interface import db_interface
from a_hold_stock_buy import stock_mkv_data
from find_grow_chance import stock_roe_rank
import pandas as pd
from find_turnover_stock import stock_turnover_rank
## 价格便宜、长期等待。



def stock_5year_price(days=365*3):
    today = datetime.datetime.now()
    bef_date = (today - datetime.timedelta(days=days)).strftime("%Y-%m-%d")
    sql = f"SELECT CODE, date, now FROM stock_days_history where date = (" \
          f"select max(date) from stock_days_history where date <= '{bef_date}' limit 1)"
    query = db_interface.stock_base.select(sql)
    result = {}
    for item in query:
        code, price = item[0], item[2]
        low = round(price*(1-0.15), 2)
        high = round(price*(1+0.15), 2)
        result[code] = {
            "price": price,
            "low": low,
            "high": high
        }
    return result



def last_stock_price(days=365*3, mkv_value=15):  ## 价格回到五年前。
    sql = "select name, code, now from stock_days_sh where date = (" \
          "select max(date) from stock_days_sh) union " \
          "select name, code, now from stock_days_sz where date = (" \
          "select max(date) from stock_days_sz)"
    query = db_interface.stock_base.select(sql)
    stock_price_range = stock_5year_price(days=days)
    stock_mkv = stock_mkv_data(mkv_value)
    result = {}
    for item in query:
        name, code, now = item
        if code in stock_price_range:
            stock_dict = stock_price_range[code]
            low = stock_dict["low"]
            high = stock_dict['high']
            bef_price = stock_dict['price']
            if now >= low and now <= high and code in stock_mkv:
                # print(name, code, now, bef_price)
                mkv_data = stock_mkv[code]
                result[code] = {
                    "name": name,
                    "price": now,
                    "bef_price": bef_price,
                    "chang_pct": round(100*(now/bef_price-1), 2),
                    "mkv": mkv_data[1],
                    "pe": mkv_data[2],
                    "ids": mkv_data[3]
                }
    # result = sorted(result, key=lambda x: x['chang_pct'], reverse=False)
    # for i, item in enumerate(result):
    #     print(i+1, item)

    return result




def low_price_and_grow(days=365*5):
    stock_dict = last_stock_price(days=days, mkv_value=35)  ## 低价，3年前的。
    stock_holder = add_stock_holder(stock_dict.keys())  ## 股东信息

    scope_dict = {}  ## 创业板的
    for stock, value in stock_dict.items():
        # if stock.startswith('300') and value['mkv'] <= 500:  ## 中小市值
        if stock:  ## 中小市值
            holder = stock_holder[stock]
            value.update(holder)
            scope_dict[stock] = value
    r = stock_roe_rank(scope_dict)
    turn_stock = stock_turnover_rank()
    turn_dict = dict([(x['code'], (x['pct'], x['vol_sum'])) for x in turn_stock])
    print(len(turn_dict))
    # print(code_list)
    for i, item in enumerate(r):
        code = item['code']
        if code in turn_dict:
            turn_data = turn_dict[code]
            item.update({
                "pct": turn_data[0],
                "vol_sum": turn_data[1]
            })
            print(i + 1, item)



def cal_stock_sum(fund_data):
    """
     [{'fund_name': '兴全合润混合(LOF)', 'fund_code': '163406', 'fund_manger': '谢治宇', 'stock_name': '顾家家居', 'stock_code': '603816', 'ratio': 2.16},]
    """
    stock_dict = {}
    for item in fund_data:
        stock_code = item['stock_code']
        if stock_code not in stock_dict:
            stock_dict[stock_code] = []
        stock_dict[stock_code].append(item)
    res = {}
    for k, v in stock_dict.items():
        ratio_sum = sum([x['ratio'] for x in v])
        _ = [x.pop('stock_code') for x in v]
        res[k] = {
            "stock_name": v[0]['stock_name'],
            "fund_sum": len(v),
            "ratio_sum": round(ratio_sum, 2),
            "detail": v
        }

    return res



def find_fund_change_stock(fund_manger=("周蔚文", "曹名长", "朱少醒")):
    sql = f"select fund_name, fund_code, fund_manger, date, stock_name, stock_code, ratio from stock_fund_holder " \
          f"where date in ('2022-12-31', '2023-03-31') and fund_manger in {fund_manger} " \
          f"order by fund_manger asc"
    if len(fund_manger) == 1:
        sql = f"select fund_name, fund_code, fund_manger, date, stock_name, stock_code, ratio from stock_fund_holder " \
              f"where date in ('2022-12-31', '2023-03-31') and fund_manger = '{fund_manger[0]}' " \
              f"order by fund_manger asc"
    print(sql)
    query = db_interface.stock_base.select(sql)
    date_dict = {}
    for item in query:
        fund_name, fund_code, fund_manger, date, stock_name, stock_code, ratio = item
        date = date.strftime("%Y-%m-%d")
        if date not in date_dict:
            date_dict[date] = []
        date_dict[date].append({
            "fund_name": fund_name,
            "fund_code": fund_code,
            "fund_manger": fund_manger,
            "stock_name": stock_name,
            "stock_code": stock_code,
            "ratio": ratio
        })

    fund_pre = cal_stock_sum(date_dict['2022-12-31'])
    fund_last = cal_stock_sum(date_dict['2023-03-31'])
    stock_list = set(list(fund_pre.keys()) + list(fund_last.keys()))

    res = []
    for stock_code in stock_list:
        default = {
            'stock_name': '',
            "fund_sum": 0,
            "ratio_sum": 0,
            "detail": []
        }
        stock_last = default
        if stock_code in fund_last:
            stock_last = fund_last[stock_code]
        stock_pre = default
        if stock_code in fund_pre:
            stock_pre = fund_pre[stock_code]
        stock_name = stock_last['stock_name'] if stock_last['stock_name'] \
                                        else stock_pre['stock_name']
        [x.pop("stock_name") for x in stock_last['detail']]
        [x.pop("stock_name") for x in stock_pre['detail']]
        res.append({
            "stock": stock_name,
            "code": stock_code,
            'delta_ratio': round(stock_last['ratio_sum'] - stock_pre['ratio_sum'], 2),
            "delta_sum": stock_last['fund_sum'] - stock_pre['fund_sum'],
            "last": stock_last['detail'],
            "pre": stock_pre['detail']
        })

    res = sorted(res, key=lambda x: x['delta_ratio'], reverse=True)
    for i, item in enumerate(res):
        print(i+1, item)
        print('------')


## 基金经理的股票持仓
def fund_manger_hold():
    manger = "朱少醒", "傅鹏博", "王崇", "谢治宇", "陈皓", "丘栋荣", "程洲", "萧楠", "毕天宇", "周蔚文", "何帅", "余广", \
             "陈一峰", "刘彦春", "张慧", "徐彦", "傅友兴", "冯明远", "曹名长", "刘格菘", "郑磊", "杨锐文", "黄兴亮", "赵蓓", "姜诚"

    # manger = "周蔚文",
    r = find_fund_change_stock(fund_manger=manger)



## 发生国企改革的股票
def stock_change_name():
    sql = "select distinct ts_code, end_date, change_reason from stock_changename " \
          "order by end_date desc limit 500"
    query = list(db_interface.stock_base.select(sql))
    stock_list = [x[0][:6] for x in query]
    stock_holder = add_stock_holder(stock_list=stock_list, mkv_value=50)
    # print(stock_holder)
    for i, item in enumerate(query):
        code, date, reason = item
        code = code[:6]
        if code in stock_holder:
            print(i, code, date, reason, stock_holder[code])



if __name__ == "__main__":
    r = low_price_and_grow(days=365*5)  ## 3年前的低价股






